package com.daimler.itffo.tables;

import java.util.InputMismatchException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.daimler.itffo.uvs.kern.IGenerator;
import com.daimler.itffo.uvs.kern.SQLStatment;
import com.daimler.itffo.uvs.kern.SQLWriter;

public class V0BRTYKZ implements IGenerator, Comparable<Object> {

	public static int COL_TYKZ_ID = 1;
	public static int COL_TYKZ_CODE = 2;
	public static int COL_TYKZ_TYP = 3;
	private String messages = new String("");
	private Integer tykz_id;
	private String tykz_code;
	private String tykz_typ;

	public V0BRTYKZ(Integer id, String code, String typ) {
		super();
		this.tykz_id = id;
		this.tykz_code = code;
		this.tykz_typ = typ;
	}

	public int compareTo(Object o) {
		V0BRTYKZ pkey = (V0BRTYKZ) o;
		int result = 0;
		result = pkey.tykz_id.compareTo(this.tykz_id);
		if (result == 0)
			result = pkey.tykz_code.compareTo(this.tykz_code);
		if (result == 0)
			result = pkey.tykz_typ.compareTo(this.tykz_typ);
		return result;
	}

	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		final V0BRTYKZ other = (V0BRTYKZ) obj;
		if (tykz_id == null) {
			if (other.tykz_id != null)
				return false;
		} else if (!tykz_id.equals(other.tykz_id))
			return false;
		if (tykz_code == null) {
			if (other.tykz_code != null)
				return false;
		} else if (!tykz_code.equals(other.tykz_code))
			if (tykz_typ == null) {
				if (other.tykz_typ != null)
					return false;
			} else if (!tykz_typ.equals(other.tykz_typ))
				return false;
		return true;
	}

	public int hashCode() {
		final int PRIME = 31;
		int result = 1;
		result = PRIME * result + ((tykz_id == null) ? 0 : tykz_id.hashCode());
		result = PRIME * result
				+ ((tykz_code == null) ? 0 : tykz_code.hashCode());
		result = PRIME * result
				+ ((tykz_typ == null) ? 0 : tykz_typ.hashCode());

		return result;
	}

	public V0BRTYKZ() {

	}

	public SQLStatment createSQLDescription() {
		SQLStatment sql = new SQLStatment();
		sql.addFragment(" --Type Beschreibung " + tykz_typ);
		sql.addFragment("------------------------------------------------------------------------");
		return sql;
	}

	public SQLStatment createSQLDeleteStatment() {
		SQLStatment sql = new SQLStatment();

		sql.addFragment("DELETE FROM V0BRTYKZ ");
		sql.addFragment("WHERE ");
		sql.addFragment("TYKZ_ID =  " + tykz_id + " ");
		sql.addFragment("AND TYKZ_CODE = '" + tykz_code + "'");
		sql.addFragment("AND TYKZ_TYP = '" + tykz_typ + "';");
		sql.addFragment(SQLWriter.NEWLINE);
		return sql;
	}


	public SQLStatment commitStatment() {
		SQLStatment commit = new SQLStatment();
		commit.addFragment("COMMIT;" + SQLWriter.NEWLINE);
		System.out.println("I'm in but don see any COMMIT!!!!!!!");
		return commit;
	}


	public String generate(Sheet sheet, String folder, String str,
			String uStr) throws Exception {
		SQLWriter fileWriterV0BRBTYKZEU;
		fileWriterV0BRBTYKZEU = new SQLWriter();
		fileWriterV0BRBTYKZEU.openFile(folder + '\\'
				+ SQLWriter.FILENAME_TYKZ_EU);

		String colChanged;
		int coltykz_id;
		String colTykzCode;
		String colTykzTyp;
		V0BRTYKZ tykz;
		Row row;
		SQLStatment sqlid;
		sqlid = new SQLStatment();
		sqlid.addFragment("SET CURRENT SQLID='" + str + "';");
		sqlid.addFragment(SQLWriter.NEWLINE);
		fileWriterV0BRBTYKZEU.write(sqlid);
		sqlid = new SQLStatment();
		int active_row = 1;
		int action;
		int rows_ignored = 0;
		int row_deleted = 0;
		int row_inserted = 0;
		int row_count = 0;
		while (true) {

			// System.out.println("Working on Row: " + active_row);
			row = sheet.getRow(active_row);

			if (isRowTheLastRow(row)) {
				fileWriterV0BRBTYKZEU.write(commitStatment());

				break;
			}

			colChanged = getValueOfStringCell(row.getCell(COL_TABLE_CHANGED));

			action = checkAction(colChanged);
			if (action == ACTION_ERROR) {
				messages = "Fehler: Die (Steuer-)Zelle "
						+ getStringPos(row.getCell(COL_TABLE_CHANGED))
						+ " muss einen der folgenden Werte enthalten:\n";
				messages += "Loeschen: 'D'\n";
				messages += "Loeschen und Einfuegen: 'I'\n";
				messages += "Keine Aktion: ''\n";
				return messages;
			}
			if (action != ACTION_NONE) {

				// System.out.println("get COL_DTCV_TYPE");
				coltykz_id = getValueOfIntCell(row.getCell(COL_TYKZ_ID));
				System.out.println(coltykz_id);

				colTykzCode = getValueOfStringCell(row.getCell(COL_TYKZ_CODE));

				System.out.println(colTykzCode);

				colTykzTyp = getValueOfStringCell(row.getCell(COL_TYKZ_TYP));
				System.out.println(colTykzTyp);

				tykz = new V0BRTYKZ(coltykz_id, colTykzCode, colTykzTyp);
				System.out.println("Objekt erzeugt");

				fileWriterV0BRBTYKZEU.write(tykz.createSQLDescription());
				fileWriterV0BRBTYKZEU.write(tykz.createSQLDeleteStatment());
				if (action == ACTION_INSERT_AND_DELETE)
					fileWriterV0BRBTYKZEU.write(tykz.createSQLStatment(false));
				row_count++;
				if (row_count % 250 == 0) {
					fileWriterV0BRBTYKZEU.write(commitStatment());
				}
			}

			if (action == ACTION_NONE)
				rows_ignored++;

			if (action == ACTION_DELETE)
				row_deleted++;

			if (action == ACTION_INSERT_AND_DELETE)
				row_inserted++;

			active_row++;

		}

		messages = "Zeilen verarbeitet: " + (active_row - 1) + "\n";
		messages += "Zeilen ignoriert: " + rows_ignored + "\n";
		messages += "Zeilen geloescht: " + row_deleted + "\n";
		messages += "Zeilen eingefuegt: " + row_inserted + "\n";

		fileWriterV0BRBTYKZEU.close();

		return messages;
	}

	@Override
	public int checkAction(String action) {
		if (action == null)
			return ACTION_ERROR;
		String upperAction = action.toUpperCase();
		if (ACTION_NONE_STRING.equals(upperAction))
			return ACTION_NONE;

		if (ACTION_DELETE_STRING.equals(upperAction))
			return ACTION_DELETE;

		if (ACTION_INSERT_AND_DELETE_STRING.equals(upperAction))
			return ACTION_INSERT_AND_DELETE;

		return ACTION_ERROR;
	}

	@Override
	public SQLStatment createSQLStatment(boolean b) {
		SQLStatment sql = new SQLStatment();

		sql.addFragment("INSERT INTO V0BRTYKZ ");
		sql.addFragment("(TYKZ_ID, ");
		sql.addFragment("TYKZ_CODE, ");
		sql.addFragment("TYKZ_TYP) ");
		sql.addFragment("VALUES (");
		sql.addValue(tykz_id, false);
		sql.addValue(tykz_code, false);
		sql.addValue(tykz_typ, true);
		sql.addFragment(SQLWriter.NEWLINE);
		return sql;
	}

	@Override
	public void checkCellStyle(Cell cell, int type)
			throws InputMismatchException {
		if (cell.getCellType() == type)
			return;

		throw new InputMismatchException("Zelle " + getStringPos(cell)
				+ " muss als " + getCellStyleStringRep(type)
				+ " definiert sein.\n" + "Zelle " + getStringPos(cell)
				+ " ist als " + getCellStyleStringRep(cell.getCellType()) + "("
				+ cell.getCellType() + ") definiert"

		);

	}

	@Override
	public String colToString(int col) {
		switch (col) {
		case 1:
			return "A";
		case 2:
			return "B";
		case 3:
			return "C";
		case 4:
			return "D";
		case 5:
			return "E";
		case 6:
			return "F";
		case 7:
			return "G";
		case 8:
			return "H";
		case 9:
			return "I";
		case 10:
			return "J";
		case 11:
			return "K";
		case 12:
			return "L";
		case 13:
			return "M";
		case 14:
			return "N";
		case 15:
			return "O";
		case 16:
			return "P";

		default:
			break;
		}
		return "Ausserhalb des Verabreitungsbereich";
	}

	@Override
	public String getCellStyleStringRep(int type) {
		switch (type) {
		case Cell.CELL_TYPE_STRING:
			return "TEXT";
		case Cell.CELL_TYPE_NUMERIC:
			return "ZAHL";
		case Cell.CELL_TYPE_FORMULA:
			return "FORMEL";
		case Cell.CELL_TYPE_BLANK:
			return "LEER";
		default:
			break;
		}
		return "UNBEKANNT";
	}

	@Override
	public String getStringPos(Cell cell) {
		return colToString(cell.getColumnIndex() + 1) + "-"
				+ (cell.getRowIndex() + 1);
	}

	@Override
	public int getValueOfIntCell(Cell cell) throws InputMismatchException {
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			String value = getValueOfStringCell(cell);
			if (value.equals("")) {
				throw new InputMismatchException("Zelle " + getStringPos(cell)
						+ " muss einen (Numerischen) Wert enthalten");
			}
			return Integer.valueOf(value).intValue();
		}

		checkCellStyle(cell, Cell.CELL_TYPE_NUMERIC);

		Double d = cell.getNumericCellValue();
		return d.intValue();
	}

	@Override
	public String getValueOfStringCell(Cell cell)
			throws InputMismatchException {
		String value = new String("");

		if (cell == null)
			return value;

		if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
			return new String("");

		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			int i = (int) cell.getNumericCellValue();
			value = String.valueOf(i);
		} else {
			throw new InputMismatchException("Zelle " + getStringPos(cell)
					+ " ist als " + getCellStyleStringRep(cell.getCellType())
					+ " definiert. Dies kann nicht verarbeit werden.");
		}

		if (value == null)
			return new String("");

		return value.trim();
	}

	@Override
	public boolean isRowTheLastRow(Row row) {
		if (row == null)
			return true;

		Cell bmCell = row.getCell(COL_TYKZ_ID);

		if (bmCell == null)
			return true;

		if (bmCell.getCellType() == Cell.CELL_TYPE_BLANK)
			return true;

		if (bmCell.getCellType() == Cell.CELL_TYPE_STRING) {
			String s = getValueOfStringCell(row.getCell(1));
			if (s == null)
				return true;
			if (!s.equals(""))
				return false;
		}
		if (bmCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			String s = Integer.toString(getValueOfIntCell(row.getCell(1)));
			if (s == null)
				return true;
			if (!s.equals(""))
				return false;
		}
		return true;

	}
}
